Imports System.Text

Friend Class DBScanQueue_Factory
    Inherits DBBase

    Public Function GetLastQueuedRequest( _
        ByVal lDatabaseID As Long, _
        ByVal lTypeID As SQLAutoDocLib.ScanSchedule.Type) As ScanQueue

        Dim oScanQueue As ScanQueue = Nothing

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("   top 1")
            .Append("   a.[ID]")
            .Append(" from")
            .Append("   ScanQueue a")
            .Append(" where")
            .Append("   a.[DatabaseID]=" & Val(lDatabaseID))
            .Append("   and a.[ScanTypeID]=" & Val(lTypeID))
            .Append("   and (not a.[ScanComplete] is null")
            .Append("   or (")

            .Append("       a.[ScanStart] is null")

            '.Append("       ---Schema Scan")
            .Append("       or (a.ScanTypeID=1 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SCHEMA_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Size Scan")
            .Append("       or (a.ScanTypeID=2 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SIZE_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Block Scan")
            .Append("       or (a.ScanTypeID=3 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='BLOCK_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Connection Scan")
            .Append("       or (a.ScanTypeID=4 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='CONNECTION_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Wait State Scan")
            .Append("       or (a.ScanTypeID=5 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='WAITSTATE_SCAN_TIMEOUT_PERIOD'))")
            .Append("       ))")

            .Append(" order by")
            .Append("       CreateDate desc")
        End With

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL.ToString)

        If Not oValue Is Nothing Then
            oScanQueue = New ScanQueue(Val(oValue))
            oScanQueue.Load()
        End If

        Return oScanQueue
    End Function

    Public Function GetOpenQueue( _
        ByVal lServerID As Long, _
        ByVal lDatabaseID As Long, _
        ByVal lSessionID As Long, _
        ByVal lScanTypeID As ScanSchedule.Type) As ScanQueue

        Dim oQueue As ScanQueue = Nothing

        Dim sSQL As String = "select"
        sSQL = sSQL & "     [ID]"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanQueue"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     ServerID=" & lServerID
        sSQL = sSQL & "     and DatabaseID=" & lDatabaseID
        sSQL = sSQL & "     and SessionID=" & lSessionID
        sSQL = sSQL & "     and ScanTypeID=" & Val(lScanTypeID)
        sSQL = sSQL & "     and ScanComplete is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            oQueue = New ScanQueue(Val(oValue))
            oQueue.Load()
        End If

        Return oQueue
    End Function

    Public Function GetNextQueuedRequest() As ScanQueue
        Dim oScanQueue As ScanQueue = Nothing

        Dim sSQL As New Stringbuilder("select")
        With sSQL
            .Append("   top 1")
            .Append("   a.[ID]")
            .Append(" from")
            .Append("   ScanQueue a")
            .Append(" where")
            .Append("   a.[ScanComplete] is null")
            .Append("   and (")

            .Append("       a.[ScanStart] is null")

            '.Append("       ---Schema Scan")
            .Append("       or (a.ScanTypeID=1 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SCHEMA_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Size Scan")
            .Append("       or (a.ScanTypeID=2 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SIZE_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Block Scan")
            .Append("       or (a.ScanTypeID=3 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='BLOCK_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Connection Scan")
            .Append("       or (a.ScanTypeID=4 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='CONNECTION_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Wait State Scan")
            .Append("       or (a.ScanTypeID=5 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='WAITSTATE_SCAN_TIMEOUT_PERIOD'))")
            .Append("       )")

            .Append(" order by")
            .Append("       CreateDate")
        End With

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL.ToString)

        If Not oValue Is Nothing Then
            oScanQueue = New ScanQueue(Val(oValue))
            oScanQueue.Load()
        End If

        Return oScanQueue
    End Function

    Public Function GetNextQueuedRequest( _
                            ByVal lDatabaseID As Long, _
                            ByVal lScanTypeID As ScanSchedule.Type) As ScanQueue

        Dim oScanQueue As ScanQueue = Nothing

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("   top 1")
            .Append("   a.[ID]")
            .Append(" from")
            .Append("   ScanQueue a")
            .Append(" where")
            .Append("   a.[DatabaseID]=" & lDatabaseID)
            .Append("   and a.[ScanTypeID]=" & Val(lScanTypeID))
            .Append("   and a.[ScanComplete] is null")
            .Append("   and (")

            .Append("       a.[ScanStart] is null")

            '.Append("       ---Schema Scan")
            .Append("       or (a.ScanTypeID=1 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SCHEMA_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Size Scan")
            .Append("       or (a.ScanTypeID=2 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SIZE_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Block Scan")
            .Append("       or (a.ScanTypeID=3 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='BLOCK_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Connection Scan")
            .Append("       or (a.ScanTypeID=4 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='CONNECTION_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Wait State Scan")
            .Append("       or (a.ScanTypeID=5 and datediff(minute,a.[ScanStart],current_timestamp)>")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='WAITSTATE_SCAN_TIMEOUT_PERIOD'))")
            .Append("       )")

            .Append(" order by")
            .Append("       CreateDate")
        End With

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL.ToString)

        If Not oValue Is Nothing Then
            oScanQueue = New ScanQueue(Val(oValue))
            oScanQueue.Load()
        End If

        Return oScanQueue
    End Function

    Public Function GetInprocessRequest( _
                        ByVal lDatabaseID As Long, _
                        ByVal lScanTypeID As ScanSchedule.Type) As ScanQueue

        Dim oScanQueue As ScanQueue = Nothing

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("   top 1")
            .Append("   a.[ID]")
            .Append(" from")
            .Append("   ScanQueue a")
            .Append(" where")
            .Append("   a.[DatabaseID]=" & lDatabaseID)
            .Append("   and a.[ScanTypeID]=" & Val(lScanTypeID))
            .Append("   and a.[ScanComplete] is null")
            .Append("   and not a.[ScanStart] is null")
            .Append("   and (")

            '.Append("       ---Schema Scan")
            .Append("       (a.ScanTypeID=1 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SCHEMA_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Size Scan")
            .Append("       or (a.ScanTypeID=2 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='SIZE_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Block Scan")
            .Append("       or (a.ScanTypeID=3 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='BLOCK_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Connection Scan")
            .Append("       or (a.ScanTypeID=4 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='CONNECTION_SCAN_TIMEOUT_PERIOD'))")

            '.Append("       ---Wait State Scan")
            .Append("       or (a.ScanTypeID=5 and datediff(minute,a.[ScanStart],current_timestamp)<")
            .Append(" 	        (select convert(int,n.[value]) from [Configuration] n where n.[name]='WAITSTATE_SCAN_TIMEOUT_PERIOD'))")
            .Append("       )")

            .Append(" order by")
            .Append("       CreateDate desc")
        End With

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL.ToString)

        If Not oValue Is Nothing Then
            oScanQueue = New ScanQueue(Val(oValue))
            oScanQueue.Load()
        End If

        Return oScanQueue
    End Function

    Public Sub RemoveCompletedRequests( _
                    ByVal iMinAgeInDays As Integer)

        Dim sSQL As String = "delete from"
        sSQL = sSQL & "     ScanQueue"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     not ScanComplete is null"
        sSQL = sSQL & "     and datediff(day,ScanComplete,current_timestamp)>" & iMinAgeInDays

        MyBase.ExecuteNonQuery(sSQL)
    End Sub

    Public Sub RemoveTimedoutRequests()

        Dim sSQL As String = "update ScanQueue"
        'sSQL = sSQL & "    set ScanStart=null "
        sSQL = sSQL & " where"
        sSQL = sSQL & "     ScanComplete is null"
        'sSQL = sSQL & "     and datediff(minute,ScanStart,current_timestamp)>" & iMinAgeInMinutes

        MyBase.ExecuteNonQuery(sSQL)
    End Sub

    Public Function GetCountOfIncompleteRequestsForSession(ByVal lSessionID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As String = "select"
        sSQL = sSQL & "     count(distinct [ID])"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanQueue"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     SessionID=" & lSessionID
        sSQL = sSQL & "     and ScanStart is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function

    Public Function GetCountOfIncompleteRequestsForDatabase(ByVal lDatabaseID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As String = "select"
        sSQL = sSQL & "     count(distinct [ID])"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanQueue"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     DatabaseID=" & lDatabaseID
        sSQL = sSQL & "     and ScanStart is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function

    Public Function GetCountOfIncompleteRequestsForServer(ByVal lServerID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As String = "select"
        sSQL = sSQL & "     count(distinct [ID])"
        sSQL = sSQL & " from"
        sSQL = sSQL & "     ScanQueue"
        sSQL = sSQL & " where"
        sSQL = sSQL & "     ServerID=" & lServerID
        sSQL = sSQL & "     and ScanStart is null"

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL)

        If Not oValue Is Nothing Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function
End Class
